package com.cet.designapplication.repo;

import com.cet.designapplication.pojo.ProductItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

/**
 * @author NSOC
 */

@Repository
public interface ProductItemRepository extends JpaRepository<ProductItem, Integer> {

    @Modifying
    @Query(value = "insert into product_item(id, name, pid) values ((select max(id)+1 from product_item),?1, ?2)", nativeQuery = true)
    void addItem(String name, int pid);

    @Modifying
    @Query(value = " with recursive temp(id, pid) as (\n" +
            "select id, pid from product_item where id = ?1 \n" +
            "union all\n" +
            "select t.id, t.pid from product_item as t inner join temp on t.pid = temp.id\n" +
            ")\n" +
            "delete from product_item where id in (select id from temp)", nativeQuery = true)
    void delItem(int id);

    ProductItem findByNameAndPid(String name, int pid);
}
